"""
    计算PB_ROE策略
"""
import datetime
import pandas as pd
import numpy as np
from data_resource.data_bases import engine, FactorReturnWgt, FactorVolskew
from utilities.utilities_statics import factor_standardize
from strategy.pb_roe import stockPool_raw
from utilities.utilities_func import get_session


def get_stocklist(enddate: str, initial_pool: list) -> list:
    """
        估值因子股票池：重资产股票=(固定资产比率+资本密集度+资产负债率+存货周转率)前50% U 剔除微利股票=净利润>1000万
        资本密度=总资产/营业收入; 存货周转率=营业总收入/平均存货价值 --> 平均存货价值使用当期存货值简单替代
        * enddate: str %Y-%m-%d
    """

    _sql = f"""
                    with bs_quarter as (
                        select iq.ticker, iq.f_ann_date, iq.end_date, iq.fix_assets/iq.total_assets as asset_rate, iq.total_assets,
                        -- 资产负债率
                        iq.total_liab/iq.total_assets as liab_rate, iq.inventories, iq.total_assets - iq.total_liab as net_assets
                        from (
                            select *, row_number() over (
                                partition by ticker, end_date
                                order by update_flag desc, f_ann_date
                            ) as rn
                            from quant_research.financials_bs_lt
                        ) iq
                        where iq.rn=1 and iq.total_assets>0 and iq.inventories>0
                    ),
                    is_quarter as (
                        select iq.ticker, iq.f_ann_date, iq.end_date, iq.total_revenue, iq.n_income_attr_p
                        from (
                            select *, row_number() over (
                                partition by ticker, end_date
                                order by update_flag desc, f_ann_date
                            ) as rn
                            from quant_research."financials_incomeState_quarter"
                        ) as iq
                        where iq.rn=1 and iq.total_revenue>0
                    )

                    select a.ticker as code, a.f_ann_date, a.end_date, a.asset_rate, a.total_assets/b.total_revenue as capital_density,
                    a.liab_rate, 360/(b.total_revenue/a.inventories) as turnover_days, b.n_income_attr_p, 
                    b.n_income_attr_p/a.net_assets as roe_q
                    from bs_quarter as a
                    left join is_quarter as b
                        on a.ticker=b.ticker and a.end_date=b.end_date
                    where a.end_date='{enddate}' and a.net_assets is not null
                """
    # 对重资产模式衡量指标标准化后，等权加总处理
    _raw = pd.read_sql(_sql, engine)
    _raw['capital_density'] = factor_standardize(_raw['capital_density'], extreme=False)
    _raw['liab_rate'] = factor_standardize(_raw['liab_rate'], extreme=False)
    _raw['asset_rate'] = factor_standardize(_raw['asset_rate'], extreme=False)
    _raw['turnover_days'] = factor_standardize(_raw['turnover_days'], extreme=False)
    _raw['score'] = _raw['capital_density'] + _raw['liab_rate'] + _raw['asset_rate'] + _raw['turnover_days']
    # 重资产模式取前50%的股票
    _raw.sort_values(by='score', ascending=False, inplace=True)
    _shape1 = np.floor(_raw.shape[0] * 0.5)
    pool_heavy_assets = set(_raw.iloc[:int(_shape1), 0].tolist())
    # 剔除归母净利润小于1000万的股票
    pool_profitable = set(_raw[_raw['n_income_attr_p'] > 10000000]['code'].tolist())
    # ROE-Q门槛设置,年化ROE>8
    pool_roe = set(_raw[_raw['roe_q'] > 0.02]['code'].tolist())
    pool1 = pool_heavy_assets | pool_profitable
    pool = pool1 & pool_roe
    # 与初始股票池做交集
    strategy_pool = list(pool & set(initial_pool))

    if pd.to_datetime(enddate).date() > pd.to_datetime('2016-03-31').date():
        # 对股票池使用业绩超预期因子靠前因子
        _pool = tuple(strategy_pool)
        _sql2 = f"""
            select code
            from quant_research."factor_earningSuprise"
            where trading = (select max(trading) from quant_research."factor_earningSuprise")
                and code in {_pool}
            order by signal desc
            limit 100
        """
        return pd.read_sql(_sql2, engine)['code'].tolist()
    else:
        raise ValueError('盈利超预期因子没有20160331以前数据')


def strategy_enhance(stock_pool: list, last_tradingDate: datetime.date):
    """
    策略增强：
        - 计算因子：60日换手率加权累计收益率 factor_returnWgt；20日成交量加权收益率偏度
    """
    with get_session(engine) as session:
        _f = session.query(
            FactorReturnWgt.trading,
            FactorReturnWgt.code,
            FactorReturnWgt.signal,
            FactorVolskew.signal_volReturn,
        ).join(
            FactorVolskew,
            (FactorReturnWgt.code == FactorVolskew.code) & (FactorReturnWgt.trading == FactorVolskew.trading)
        ).where(
            FactorReturnWgt.code.in_(stock_pool),
            FactorReturnWgt.trading == last_tradingDate
        ).all()

    _f = pd.DataFrame(_f)
    _f['signal'] = factor_standardize(_f['signal'])
    _f['signal_volReturn'] = factor_standardize(_f['signal_volReturn'])
    # _f['riv_up'] = factor_standardize(_f['riv_up'])
    _f['combined_signal'] = _f['signal'] + _f['signal_volReturn']  # 等权合并因子, 负向因子
    _f.sort_values(by='combined_signal', ascending=True, inplace=True)
    _f.reset_index(inplace=True, drop=True)
    return _f


def main():
    stock_pool = stockPool_raw('csi800_1000')
    stock_list = get_stocklist(enddate="2025-06-30", initial_pool=stock_pool)
    signals = strategy_enhance(stock_list, datetime.date(2025, 8, 29))
    return signals


if __name__ == '__main__':
    signal = main()
    stocks = signal.iloc[:30, 1].values.tolist()
    print(signal)
    print("---------------- 前30得分股票 ----------------")
    print(','.join(stocks))


